package com.his.util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class SimplyaDbTest {
	
	public static void main(String[] args) throws ClassNotFoundException, SQLException {
		
        Connection conn = getConnection();
        PreparedStatement pstmt = null;
        ResultSet rs = null;
	    try {
	    	
	    	/*select * from    dbo.buss
	    	select * from   dbo.CarSchema
	    	select * from  dbo.CNTBUSS
	    	select * from  dbo.CarStdCode
	    	select * from  dbo.CarBuss
	    	select * from dbo.CarStdGroup
	    	select * from dbo.CarStdGrp*/
	    	
	    	//initBuss(conn,pstmt,rs);
	    	//initCarBuss(conn,pstmt,rs);
	    	//initCarSchema(conn,pstmt,rs);
	    	//initCarStdGroup(conn,pstmt,rs);
	    	//initCarStdGrp(conn,pstmt,rs);
	    	//initCarStdCode(conn,pstmt,rs);
	    	
	    	initwfparam(conn,pstmt,rs);
	       
        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            cleanUP(rs, pstmt, conn);
        }
    }
	
	private static void initBuss(Connection conn, PreparedStatement pstmt, ResultSet rs) throws SQLException {
    	String sql = "select * from   Buss";
    	 pstmt = conn.prepareStatement(sql);
         //pstmt.setString(1, u.getLoginName());
         rs = pstmt.executeQuery();
         String str = "";
         int i = 0;
         while (rs.next()) {
         	str = "insert into Buss(BussNo,BussName,BussDir,MGNo,AppNo) values(";
         	str += "'"+rs.getString(1).trim()+"',";
         	str += "'"+rs.getString(2).trim()+"',";
         	str += rs.getString(3).trim()+",";
         	str += "'"+rs.getString(4).trim()+"',";
         	str += "'"+rs.getString(5).trim()+"');";
         	System.out.println(str);
         	i++;
         }
         
         System.out.println(i);
		
	}
	
	private static void initCarStdGroup(Connection conn, PreparedStatement pstmt, ResultSet rs) throws SQLException {
    	String sql = "select * from   CarStdGroup";
    	 pstmt = conn.prepareStatement(sql);
         //pstmt.setString(1, u.getLoginName());
         rs = pstmt.executeQuery();
         String str = "";
         int i = 0;
         while (rs.next()) {
         	str = "insert into CarStdGroup(SGNo,SGName) values(";
         	str += "'"+rs.getString(1).trim()+"',";
         	str += "'"+rs.getString(2).trim()+"');";
         	System.out.println(str);
         	i++;
         }
         
         System.out.println(i);
		
	}
	
	private static void initCarStdGrp(Connection conn, PreparedStatement pstmt, ResultSet rs) throws SQLException {
    	String sql = "select * from   CarStdGrp";
    	 pstmt = conn.prepareStatement(sql);
         //pstmt.setString(1, u.getLoginName());
         rs = pstmt.executeQuery();
         String str = "";
         int i = 0;
         while (rs.next()) {
         	str = "insert into CarStdGrp(SGNo,SGName) values(";
         	str += "'"+rs.getString(1).trim()+"',";
         	str += "'"+rs.getString(2).trim()+"');";
         	System.out.println(str);
         	i++;
         }
         
         System.out.println(i);
		
	}
	
	private static void initwfparam(Connection conn, PreparedStatement pstmt, ResultSet rs) throws SQLException {
    	String sql = "select * from   wfparam";
    	 pstmt = conn.prepareStatement(sql);
         //pstmt.setString(1, u.getLoginName());
         rs = pstmt.executeQuery();
         String str = "";
         int i = 0;
         while (rs.next()) {
         	str = "insert into wfparam(ParamNo,AppNo,ParamName,ParamVal,ParamTxt,InpMode,ValList) values(";
         	str += "'"+rs.getString(1).trim()+"',";
         	str += "'"+rs.getString(2).trim()+"',";
         	str += "'"+rs.getString(3).trim()+"',";
         	str += "'"+rs.getString(4).trim()+"',";
         	str += "'"+rs.getString(5).trim()+"',";
         	str += rs.getString(6).trim()+",";
         	str += "'"+rs.getString(7).trim()+"');";
         	System.out.println(str);
         	i++;
         }
         
         System.out.println(i);
		
	}
    
	private static void initCarStdCode(Connection conn, PreparedStatement pstmt, ResultSet rs) throws SQLException {
    	String sql = "select * from   CarStdCode";
    	 pstmt = conn.prepareStatement(sql);
         //pstmt.setString(1, u.getLoginName());
         rs = pstmt.executeQuery();
         String str = "";
         int i = 0;
         while (rs.next()) {
         	str = "insert into CarStdCode(SGNo,SCode,SName) values(";
         	str += "'"+rs.getString(1).trim()+"',";
         	str += "'"+rs.getString(2).trim()+"',";
         	str += "'"+rs.getString(3).trim()+"');";
         	System.out.println(str);
         	i++;
         }
         
         System.out.println(i);
		
	}

	private static void initCarSchema(Connection conn, PreparedStatement pstmt, ResultSet rs) throws SQLException {
    	String sql = "select * from   CarSchema";
    	 pstmt = conn.prepareStatement(sql);
         //pstmt.setString(1, u.getLoginName());
         rs = pstmt.executeQuery();
         String str = "";
         int i = 0;
         while (rs.next()) {
         	str = "insert into CarSchema(ElementId,BussNo,SGNo,TabName,FldName,ElementKey,Caption,FldType,FldLen,DigLen,IsKeep,ShowOrd,NewEnable,EditEnable,DefaultText,ValList,DispWidth,InQuery) values(";
         	str += rs.getString(1).trim()+",";
         	str += "'"+rs.getString(2).trim()+"',";
         	str += "'"+rs.getString(3).trim()+"',";
         	str += "'"+rs.getString(4).trim()+"',";
         	str += "'"+rs.getString(5).trim()+"',";
         	str += rs.getString(6).trim()+",";
         	str += "'"+rs.getString(7).trim()+"',";
         	str += rs.getString(8).trim()+",";
         	str += rs.getString(9).trim()+",";
         	str += rs.getString(10).trim()+",";
         	str += rs.getString(11).trim()+",";
         	str += rs.getString(12).trim()+",";
         	str += rs.getString(13).trim()+",";
         	str += rs.getString(14).trim()+",";
         	str += "'"+rs.getString(15).trim()+"',";
         	str += "'',";
         	str += rs.getString(17).trim()+",";
         	str += rs.getString(18).trim()+");";
         	System.out.println(str);
         	i++;
         }
         
         System.out.println(i);
		
	}
    
    private static void initCarBuss(Connection conn, PreparedStatement pstmt, ResultSet rs) throws SQLException {
    	String sql = "select * from   CarBuss";
    	 pstmt = conn.prepareStatement(sql);
         //pstmt.setString(1, u.getLoginName());
         rs = pstmt.executeQuery();
         String str = "";
         int i = 0;
         while (rs.next()) {
         	str = "insert into CarBuss(BussNo,BussName,BussDir,MGNo,AppNo) values(";
         	str += "'"+rs.getString(1).trim()+"',";
         	str += "'"+rs.getString(2).trim()+"',";
         	str += rs.getString(3).trim()+",";
         	str += "'"+rs.getString(4).trim()+"',";
         	str += "'"+rs.getString(5).trim()+"');";
         	System.out.println(str);
         	i++;
         }
         
         System.out.println(i);
		
	}

	private static Connection getConnection() throws ClassNotFoundException, SQLException{
    	Connection conn=null;
    	//Class.forName("oracle.jdbc.driver.OracleDriver"); 
    	Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver"); 
    	//conn = DriverManager.getConnection("jdbc:oracle:thin:@192.168.0.45:1521:hnair","credittest","11111111");
    	//conn = DriverManager.getConnection("jdbc:oracle:thin:@192.168.0.45:1521:hnair","nstcsa","11111111");
    	//conn = DriverManager.getConnection("jdbc:oracle:thin:@192.168.0.45:1521:credittest","nstcsa","11111111");
    	conn = DriverManager.getConnection("jdbc:sqlserver://localhost:1433;DatabaseName=YqCredit","sa","11111111");
    	return conn;
    }

    private static void cleanUP(ResultSet rs, PreparedStatement pstmt, Connection conn) {
        try {
            if (rs != null) {
                rs.close();
            }
            if (pstmt != null) {
                pstmt.close();
            }
            if (conn != null) {
                conn.close();
            }
        } catch (SQLException ex) {
            System.out.println(ex.getMessage());
        }
    }
}